﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace CompressionEstimator
{
    public partial class Results : Form
    {
        #region Properties
        public int ConnectionTimeout { get; set; }
        public int ExecutionTimeout { get; set; }
        public decimal Threshold { get; set; }
        public bool CheckIndexUsage { get; set; }
        public string ServerName { get; set; }
        public string DatabaseName { get; set; }
        // Manually implement this property, since we need to set SavedConnectionString based on this set
        SqlConnectionInfo connectionInfo;
        public SqlConnectionInfo ConnectionInfo
        {
            get { return connectionInfo; }
            set { connectionInfo = value; SavedConnectionString = connectionInfo.ConnectionString; }
        }
        public string SavedConnectionString { get; set; }
        //public List<int> SelectedRows = new List<int>();
        public StringBuilder sqlScript  = new StringBuilder();
       
#endregion Properties

        #region Form Methods
        public Results()
        {
            InitializeComponent();
            // Define columns, styles, etc.
            SetupDataGridView();
        }
        #endregion Form Methods

        #region Event Handlers
        private void Close_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        private void Process_Click(object sender, EventArgs e)
        {
            // Don't allow multiple clicks
            btnProcess.Enabled = false;

            // This is going to take awhile - change to wait cursor
            Cursor current = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;

            // Connect to SQL Server
            SqlConnection con = new SqlConnection(ConnectionInfo.ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandTimeout = ExecutionTimeout;

            SqlParameter schemaNameParameter = new SqlParameter();
            schemaNameParameter.ParameterName = "@schema_name";
            schemaNameParameter.SqlDbType = SqlDbType.NVarChar;
            schemaNameParameter.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(schemaNameParameter);

            SqlParameter objectNameParameter = new SqlParameter();
            objectNameParameter.ParameterName = "@object_name";
            objectNameParameter.SqlDbType = SqlDbType.NVarChar;
            objectNameParameter.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(objectNameParameter);

            SqlParameter indexIDParameter = new SqlParameter();
            indexIDParameter.ParameterName = "@index_id";
            indexIDParameter.SqlDbType = SqlDbType.Int;
            indexIDParameter.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(indexIDParameter);

            SqlParameter partitionNumberParameter = new SqlParameter();
            partitionNumberParameter.ParameterName = "@partition_number";
            partitionNumberParameter.SqlDbType = SqlDbType.Int;
            partitionNumberParameter.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(partitionNumberParameter);

            SqlParameter dataCompressionTypeParameter = new SqlParameter();
            dataCompressionTypeParameter.ParameterName = "@data_compression";
            dataCompressionTypeParameter.SqlDbType = SqlDbType.NVarChar;
            dataCompressionTypeParameter.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(dataCompressionTypeParameter);

            con.Open();
            // Do processing here
            Server s = new Server(ConnectionInfo.ServerName);
            Database d = s.Databases[ConnectionInfo.DatabaseName];
            TableCollection t = d.Tables;
            try
            {
                foreach (Table tbl in t)
                {
                    //Do space compression for table
                    cmd.Parameters["@schema_name"].Value = tbl.Schema;
                    cmd.Parameters["@object_name"].Value = tbl.Name;

                    // Do all indexes
                    cmd.Parameters["@index_id"].Value = DBNull.Value;

                    // Do all partitions
                    cmd.Parameters["@partition_number"].Value = DBNull.Value;
                    for (int types = 0; types < 2; types++)
                    {
                        // Call the stored procedure for both PAGE and ROW estimates
                        string compressionType = (types == 0) ? "PAGE" : "ROW";
                        cmd.Parameters["@data_compression"].Value = compressionType;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "sp_estimate_data_compression_savings";
                        // Retrieve our results and display to the user
                        using (SqlDataReader rdr = cmd.ExecuteReader())
                        {
                            DisplayRows(rdr, compressionType);
                        }
                        dgResults.ClearSelection();
                    }
                }
            }
            catch (SqlException ex)
            {
                SqlError error = ex.Errors[0];
                if(error.Number == 207)
                {
                    MessageBox.Show("Due to a defect in the system stored procedure used to estimate compression savings, tables that contain columns with embedded special characters cannot be processed.\r\nThis defect has been reported to the Product Team.","Compression Estimator",MessageBoxButtons.OK,MessageBoxIcon.Error);
                }
            }
            finally
            {
                con.Close();
            }
            Cursor.Current = current;
            btnSave.Enabled = true;
            btnScript.Enabled = true;
            btnProcess.Enabled = false;
        }
        private void Save_Click(object sender, EventArgs e)
        {
            // Save Dialog
            saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.CreatePrompt = false;
            saveFileDialog1.FileName = DatabaseName + "CompressionResults";
            saveFileDialog1.DefaultExt = ".csv";
            saveFileDialog1.OverwritePrompt = false;
            saveFileDialog1.InitialDirectory = @"C:\temp\";
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string filename = saveFileDialog1.FileName;
                FileStream fs = File.Open(filename,FileMode.OpenOrCreate,FileAccess.Write);
                string line = "Schema,Object,Type,Index #,Part #, Before (kb), After(kb), Pct Savings, Index Maint Ratio\r\n";
                AddText(fs, line);

                foreach(DataGridViewRow r in dgResults.Rows)
                {
                    line = "";
                    // ignore the checkbox column
                    for(int c =0; c < 9;c++)
                    {
                        line += r.Cells[c].Value.ToString();
                        line += ",";
                    }
                    line = line.Substring(0, line.Length - 1);
                    line += "\r\n";
                    AddText(fs, line);
                }
                fs.Flush();
                fs.Close();
            }
            dgResults.Rows.Clear();
            btnProcess.Enabled = true;
            btnSave.Enabled = false;

        }
        private void Script_Click(object sender, EventArgs e)
        {
            sqlScript.Remove(0, sqlScript.Length);
            sqlScript.Append("-- This compression script was created by Compression Estimator\r\n");
            foreach (DataGridViewRow r in dgResults.Rows)
            {
                DataGridViewCheckBoxCell c = (DataGridViewCheckBoxCell)r.Cells["Script"];
                if (Convert.ToBoolean(c.Value) == true)
                {
                    Script(r);
                }
            }
            SaveSQLScript();
        }
        private void Databases_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbDatabases.Text != "")
            {
                btnProcess.Enabled = true;
                DatabaseName = cbDatabases.Text;
                connectionInfo.DatabaseName = DatabaseName;
            }
        }
        private void IndexUsage_CheckedChanged(object sender, EventArgs e)
        {
            CheckIndexUsage = chkIndexUsage.Checked;
        }
        private void Results_Load(object sender, EventArgs e)
        {
            ServerConnection c = new ServerConnection(ConnectionInfo);
            Server s = new Server(c);
            foreach (Database d in s.Databases)
            {
                //Compression was added with SQL Server 2008. If a database is not compat level 100 then don't display it
                // Work item 312 - don't exclude databases by compat level
                // Instead only allow connecting to Servers that are 2008 or later
                //if (d.CompatibilityLevel >= CompatibilityLevel.Version100)
                {
                    cbDatabases.Items.Add(d.Name);
                }
            }
        }
        private void Threshold_ValueChanged(object sender, EventArgs e)
        {
            Threshold = nudThreshold.Value;
        }
        #endregion Event Handlers

        #region Helper Functions
        private static void AddText(FileStream fs, string value)
        {
            byte[] info = new UTF8Encoding(true).GetBytes(value);
            fs.Write(info, 0, info.Length);
        }
        private decimal CheckIndexMetrics(string objectName, string indexNo)
        {
            // When requested, we will evaluate how often the indexes are used in comparision
            // to how often they are updated
            decimal maint = 0.0M;
            // Could have used MARS, but decided to use separate connection
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(SavedConnectionString);
            csb.InitialCatalog = DatabaseName;
            using (SqlConnection con = new SqlConnection(csb.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandTimeout = ExecutionTimeout;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "SELECT (user_updates + 0.0) / (user_seeks + user_scans + user_lookups) as Maint FROM sys.dm_db_index_usage_stats "+
                        " WHERE database_id = db_id(@database_name) AND object_name(object_id) = @object_name AND index_id = @index_id;";

                    SqlParameter databaseNameParameter = new SqlParameter();
                    databaseNameParameter.ParameterName = "@database_name";
                    databaseNameParameter.SqlDbType = SqlDbType.NVarChar;
                    databaseNameParameter.Direction = ParameterDirection.Input;
                    databaseNameParameter.Value = DatabaseName;
                    cmd.Parameters.Add(databaseNameParameter);

                    SqlParameter objectNameParameter = new SqlParameter();
                    objectNameParameter.ParameterName = "@object_name";
                    objectNameParameter.SqlDbType = SqlDbType.NVarChar;
                    objectNameParameter.Direction = ParameterDirection.Input;
                    objectNameParameter.Value = objectName;
                    cmd.Parameters.Add(objectNameParameter);

                    SqlParameter indexIDParameter = new SqlParameter();
                    indexIDParameter.ParameterName = "@index_id";
                    indexIDParameter.SqlDbType = SqlDbType.Int;
                    indexIDParameter.Direction = ParameterDirection.Input;
                    indexIDParameter.Value = Convert.ToInt32(indexNo);
                    cmd.Parameters.Add(indexIDParameter);

                    con.Open();
                    try
                    {
                        SqlDataReader rdr = cmd.ExecuteReader();
                        if(rdr.Read())
                        {
                            maint = rdr.GetDecimal(0);
                        }
                        rdr.Close();
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                    }
                }
            }
            return maint;
        }
        private void DisplayRows(SqlDataReader rdr,string compressionType)
        {
            while (rdr.Read())
            {
                string schemaname = rdr.GetString(1);
                string objectname = rdr.GetString(0);
                string indexNo = Convert.ToString(rdr[2]);
                string partNo = Convert.ToString(rdr[3]);
                decimal before = Convert.ToDecimal(rdr[4]);
                decimal after = Convert.ToDecimal(rdr[5]);
                decimal maint =0.0M;
                if (CheckIndexUsage == true)
                {
                    maint = CheckIndexMetrics(objectname, indexNo);
                }
                //Account for empty tables
                if (before == 0)
                {
                    after = 1;
                    before = 1;
                }
                decimal pct = (((before - after) / before) * 100);
                string strBefore = Convert.ToString(before);
                string strAfter = Convert.ToString(after);
                string strPct = string.Format("{0:F2}", pct);
                string strmaintOverhead = string.Format("{0:F4}", maint);
                if (pct > Threshold)
                {
                    if (CheckIndexUsage == false)
                    {
                        strmaintOverhead = "Not evaluated";
                    }
                    string[] row = { schemaname, objectname, compressionType, indexNo, partNo, strBefore, strAfter, strPct, strmaintOverhead };
                    dgResults.Rows.Add(row);
                }
                Application.DoEvents();
                Cursor.Current = Cursors.WaitCursor;
            }
        }
        private void SetupDataGridView()
        {
            this.Controls.Add(dgResults);

            dgResults.ColumnCount = 9;

            dgResults.Name = "dgResults";

            dgResults.Columns[0].Name = "Schema";
            dgResults.Columns[1].Name = "Object";
            dgResults.Columns[1].Width = 200;
            dgResults.Columns[2].Name = "Type";
            dgResults.Columns[2].Width = 50;
            dgResults.Columns[3].Name = "Index #";
            dgResults.Columns[3].Width = 50;
            dgResults.Columns[4].Name = "Part. #";
            dgResults.Columns[4].Width = 50;
            dgResults.Columns[5].Name = "Current size (KB)";
            dgResults.Columns[5].Width = 100;
            dgResults.Columns[6].Name = "Compressed Size (KB)";
            dgResults.Columns[6].Width = 100;
            dgResults.Columns[7].Name = "% Savings";
            dgResults.Columns[7].Width = 75;
            dgResults.Columns[8].Name = "Index Maintenace Ratio";
            dgResults.Columns[8].Width = 100;

            DataGridViewCheckBoxColumn column = new DataGridViewCheckBoxColumn();
            {
                column.HeaderText = "Script This Option?";
                column.Name = "Script";
                column.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
                column.FlatStyle = FlatStyle.Standard;
                column.ThreeState = false;
                column.CellTemplate = new DataGridViewCheckBoxCell();
            }
            dgResults.Columns.Add(column);
            dgResults.Columns[9].Width = 75;
            //dgResults.Columns[9].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;

            dgResults.MultiSelect = false;
            dgResults.ClearSelection();
            dgResults.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders;
            dgResults.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single;
            dgResults.CellBorderStyle = DataGridViewCellBorderStyle.Single;
            dgResults.GridColor = Color.DarkGreen;

            dgResults.ColumnHeadersDefaultCellStyle.Font = new Font(dgResults.Font, FontStyle.Bold);

            // Set the selection background color for all the cells.
            dgResults.DefaultCellStyle.SelectionBackColor = Color.DarkGreen;
            dgResults.DefaultCellStyle.SelectionForeColor = Color.White;

            // Set RowHeadersDefaultCellStyle.SelectionBackColor so that its default
            // value won't override DataGridView.DefaultCellStyle.SelectionBackColor.
            dgResults.RowHeadersDefaultCellStyle.SelectionBackColor = Color.Empty;
            dgResults.RowHeadersDefaultCellStyle.SelectionForeColor = Color.Empty;

            // Set the background color for all rows and for alternating rows. 
            // The value for alternating rows overrides the value for all rows. 
            dgResults.RowsDefaultCellStyle.BackColor = Color.White;
            dgResults.RowsDefaultCellStyle.ForeColor = Color.DarkGreen;
            dgResults.AlternatingRowsDefaultCellStyle.BackColor = Color.LightGreen;
            dgResults.AlternatingRowsDefaultCellStyle.ForeColor = Color.Black;

            // Set the row and column header styles.
            dgResults.ColumnHeadersDefaultCellStyle.BackColor = Color.Navy;
            dgResults.ColumnHeadersDefaultCellStyle.ForeColor = Color.White;
        }

        private void Script(DataGridViewRow row)
        {
            
            StringBuilder scriptFragment = new StringBuilder();
            string[] cols = new string[10];
            RowToStringArray(row, ref cols);
            bool isPartitioned = false;
            string indexName = GetIndexName(cols, ref isPartitioned);
            // if this is a clustered index, or a heap we use ALTER TABLE to apply compression
            if (cols[3] == "1" || cols[3] == "0")
            {
                scriptFragment.Append("-- By applying " + cols[2] + " compression setting, you should obtain \r\n");
                scriptFragment.Append("-- approximately " + cols[7] + "% compression\r\n");
                scriptFragment.Append("ALTER TABLE ");
                scriptFragment.Append("["+cols[0]+"].["+cols[1]+"]\r\n");
                
                if (isPartitioned)
                {
                    scriptFragment.Append("REBUILD PARTITION = " + cols[4] + "\r\n");
                }
                else
                {
                    scriptFragment.Append("REBUILD \r\n");
                }
                scriptFragment.Append("WITH (DATA_COMPRESSION = " + cols[2] + ");\r\n");
            }
            else
            {
                // Otherwise, we use ALTER INDEX
                scriptFragment.Append("-- By applying " + cols[2] + " compression setting, you should obtain \r\n");
                scriptFragment.Append("-- approximately " + cols[7] + "% compression\r\n");
                scriptFragment.Append("ALTER INDEX " + indexName);
                scriptFragment.Append(" ON " + "["+cols[0]+"].["+cols[1]+"]\r\n");
                if (isPartitioned)
                {
                    scriptFragment.Append("REBUILD PARTITION = " + cols[4]+ " WITH (DATA_COMPRESSION = " + cols[2] +");\r\n");
                }
                else
                {
                    scriptFragment.Append("REBUILD WITH (DATA_COMPRESSION = " + cols[2] + ");\r\n");
                }
            }
            sqlScript.Append("\r\n");
            sqlScript.Append(scriptFragment.ToString());
            
        }
        // Helper to ease the getting of column values
        private void RowToStringArray(DataGridViewRow row, ref string[] cols)
        {
            for (int c = 0; c < 10; c++)
            {
                cols[c] = row.Cells[c].Value.ToString();
            }
        }
        private string GetIndexName(string[] cols,ref bool isPartitioned)
        {
         SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(SavedConnectionString);
            csb.InitialCatalog = DatabaseName;
            string indexName ="";
            using (SqlConnection con = new SqlConnection(csb.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandTimeout = ExecutionTimeout;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "SELECT   i.name AS index_name,COUNT(p.index_id) AS NumberOfPartitions FROM sys.indexes AS i INNER JOIN sys.partitions AS p ON i.object_id = p.object_id WHERE is_hypothetical = 0 AND i.index_id = @index_id AND i.object_id = OBJECT_ID(@object_name) GROUP BY i.name, p.index_id";

                    SqlParameter objectNameParameter = new SqlParameter();
                    objectNameParameter.ParameterName = "@object_name";
                    objectNameParameter.SqlDbType = SqlDbType.NVarChar;
                    objectNameParameter.Direction = ParameterDirection.Input;
                    objectNameParameter.Value = "["+cols[0]+"].["+cols[1]+"]";
                    cmd.Parameters.Add(objectNameParameter);

                    SqlParameter indexIDParameter = new SqlParameter();
                    indexIDParameter.ParameterName = "@index_id";
                    indexIDParameter.SqlDbType = SqlDbType.Int;
                    indexIDParameter.Direction = ParameterDirection.Input;
                    indexIDParameter.Value = Convert.ToInt32(cols[3]);
                    cmd.Parameters.Add(indexIDParameter);

                    con.Open();
                    try
                    {
                        SqlDataReader rdr = cmd.ExecuteReader();
                        if(rdr.Read())
                        {
                            indexName = rdr.GetString(0);
                            isPartitioned = (rdr.GetInt32(1) == 1) ?false : true;
                        }
                        rdr.Close();
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                    }
                }
            }
            return indexName;
        }
        public void SaveSQLScript()
        {
            saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.CreatePrompt = false;
            saveFileDialog1.FileName = DatabaseName + "SelectedCompressionScripts";
            saveFileDialog1.DefaultExt = ".sql";
            saveFileDialog1.OverwritePrompt = false;
            saveFileDialog1.InitialDirectory = @"C:\temp\";
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string filename = saveFileDialog1.FileName;
                FileStream fs = File.Open(filename, FileMode.OpenOrCreate, FileAccess.Write);
                
                AddText(fs, sqlScript.ToString());
                fs.Flush();
                fs.Close();
            }
        }
    }
    #endregion Helper Functions
}
